package com.yalu.read.excel.sale;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.yalu.entity.SaleData;
import org.springframework.beans.BeanUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class SaleTenEightExcelRead {

    /**
     * 测试pdf文件的创建
     *
     * @param args
     */
    public static void main(String[] args) {
        String filePath = "D://yalu//sale//";  //这里先手动把绝对路径的文件夹给补上。
        List<String> filePaths = new ArrayList<>();
        recursionFile(filePath, filePaths);
        List<SaleData> list = new ArrayList<>();
        for (String fileName : filePaths) {
            readExcel(fileName, list);
        }
        System.out.println("文件数量：" + filePaths.size() + " 数据数量: " + list.size());
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String outFileName = "D:\\yalu\\销售单_" + LocalDateTime.now().format(dtf) + ".xlsx";
        writerFils(list, outFileName);
    }

    /**
     * 读PDF文件，使用了pdfbox开源项目
     *
     * @param fileName
     */
    public static void readExcel(String fileName, List<SaleData> list) {
        File file = new File(fileName);
        FileInputStream in = null;
        InputStream inputStream;
        try {
            inputStream = new FileInputStream(fileName);
            boolean isOk = true;
            EasyExcel.readSheet(0);
            List<Object> dataList = EasyExcel.read(inputStream).headRowNumber(0).doReadAllSync();
            int size = dataList.size();
            Queue<SaleData> dataQueue = new LinkedList<>();
            for (int i = 0; i < size; i++) {
                SaleData dataNow = dataQueue.poll();
                SaleData saleData = new SaleData();
                if (null != dataNow) {
                    BeanUtils.copyProperties(dataNow, saleData);
                }
                LinkedHashMap linkedHashMap = (LinkedHashMap) dataList.get(i);
                String lineText = JSON.toJSONString(linkedHashMap);
                if (StringUtils.hasText(lineText) && lineText.contains("To :")) {
                    saleData.setName(String.valueOf(linkedHashMap.get(0)).replace("To :", "").trim());
                } else if (StringUtils.hasText(lineText) && lineText.contains("Date") && !lineText.contains("Flight Date")) {
                    String date = String.valueOf(linkedHashMap.get(14)).trim();
                    if ("null".equals(date)) {
                        date = null;
                    }
                    saleData.setDate(date);
                } else if (StringUtils.hasText(lineText) && (lineText.contains("Salesman") || lineText.contains("{Total"))) {
                    saleData.setCurrency(String.valueOf(linkedHashMap.get(12)).trim());
                    saleData.setAmount(String.valueOf(linkedHashMap.get(14)).trim());
                    if (!StringUtils.hasText(saleData.getDate())) {
                        saleData = new SaleData();
                    }
                } else if (StringUtils.hasText(lineText) && lineText.contains("date:")) {
                    String date = String.valueOf(linkedHashMap.get(7)).trim();
                    saleData.setDate(date);
                } else if (StringUtils.hasText(lineText) && (lineText.contains("Total amount"))) {
                    saleData.setAmount(String.valueOf(linkedHashMap.get(8)).trim());
                } else if (StringUtils.hasText(lineText) && (lineText.contains("contract number："))) {
                    String[] nameArray = String.valueOf(linkedHashMap.get(0)).split(": ");
                    saleData.setName(nameArray[1].trim());
                    saleData.setSaleNo(String.valueOf(linkedHashMap.get(8)).trim());
                } else if (StringUtils.hasText(lineText) && lineText.contains("DATE:")) {
                    int sizeSaleNo = linkedHashMap.size();
                    if (sizeSaleNo == 6) {
                        saleData.setDate(String.valueOf(linkedHashMap.get(5)).trim());
                    } else {
                        saleData.setDate(String.valueOf(linkedHashMap.get(4)).trim());
                    }
                } else if (StringUtils.hasText(lineText) && (lineText.contains("TOTAL:"))) {
                    int sizeSaleNo = linkedHashMap.size();
                    if (sizeSaleNo >= 8) {
                        saleData = new SaleData();
                        continue;
                    }
                    if (sizeSaleNo == 6 || sizeSaleNo == 7) {
                        String amount = String.valueOf(linkedHashMap.get(5)).trim();
                        if (StringUtils.hasText(amount) && !"null".equals(amount)) {
                            saleData.setTotal(String.valueOf(linkedHashMap.get(3)).trim());
                            saleData.setAmount(amount);
                        } else {
                            if (sizeSaleNo == 7) {
                                String amountTemp = String.valueOf(linkedHashMap.get(6)).trim();
                                if (StringUtils.hasText(amountTemp) && !"null".equals(amountTemp)) {
                                    saleData.setAmount(amountTemp);
                                } else {
                                    saleData.setTotal(String.valueOf(linkedHashMap.get(2)).trim());
                                    saleData.setAmount(String.valueOf(linkedHashMap.get(4)).trim());
                                }
                            } else {
                                saleData.setTotal(String.valueOf(linkedHashMap.get(2)).trim());
                                saleData.setAmount(String.valueOf(linkedHashMap.get(4)).trim());
                            }
                        }
                    } else {
                        saleData.setAmount(String.valueOf(linkedHashMap.get(4)).trim());
                    }
                } else if (StringUtils.hasText(lineText) && (lineText.contains("INVOICE NO.:") || lineText.contains("INVOICE NO."))) {
                    int sizeSaleNo = linkedHashMap.size();
                    if (lineText.contains("发票号码 (INVOICE NO.):")) {
                        saleData.setSaleNo(String.valueOf(linkedHashMap.get(7)).trim());
                    } else {
                        String no = String.valueOf(linkedHashMap.get(sizeSaleNo - 1)).trim();
                        if (StringUtils.hasText(no) && !"null".equals(no)) {
                            saleData.setSaleNo(no);
                        } else {
                            if (sizeSaleNo == 6) {
                                saleData.setSaleNo(String.valueOf(linkedHashMap.get(5)).trim());
                            } else if (sizeSaleNo == 7) {
                                saleData.setSaleNo(String.valueOf(linkedHashMap.get(6)).trim());
                            } else {
                                saleData.setSaleNo(String.valueOf(linkedHashMap.get(4)).trim());
                            }
                        }
                    }
                } else if (i > 3 && StringUtils.hasText(lineText) && (lineText.contains("TO") && lineText.contains("发票号："))) {
                    saleData.setName(String.valueOf(linkedHashMap.get(1)).trim());
                }
                dataQueue.offer(saleData);
                if (StringUtils.hasText(saleData.getAmount()) && StringUtils.hasText(saleData.getDate()) && StringUtils.hasText(saleData.getName())) {
                    SaleData data = dataQueue.poll();
                    data.setFileName(fileName);
                    list.add(data);
                    isOk = false;
                    continue;
                }
            }
            if (isOk) {
                System.out.printf("未处理文件:" + fileName);
            }
        } catch (Exception e) {
            System.out.println("读取PDF文件" + file.getAbsolutePath() + "生失败！" + e);
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e1) {
                }
            }
        }
    }

    private static boolean isNull(String str) {
        boolean isNull = false;
        if (StringUtils.hasText(str)) {
            if (!"null".equals(str) && !"[null".equals(str) && !"null]".equals(str)) {
                isNull = true;
            }
        }
        return isNull;
    }

    /**
     * 写入文件
     *
     * @param data
     * @param outFileName
     */
    public static void writerFils(List<SaleData> data, String outFileName) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(outFileName, SaleData.class).build();
            String sheetName = "1";
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(data, writeSheet);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            System.out.println("文件写入失败：" + e.getMessage());
        }
    }

    /**
     * 递归文件
     *
     * @param path
     * @param filePaths
     */
    public static void recursionFile(String path, List<String> filePaths) {
        if (StringUtils.hasText(path)) {
            File file = new File(path);
            for (File f : file.listFiles()) {
                if (f.isDirectory()) {
                    recursionFile(f.getPath(), filePaths);
                } else {
                    if (f.getPath().toLowerCase().contains(".xls") || f.getPath().toLowerCase().contains(".xlsx")) {
                        filePaths.add(f.getPath());
                    }
                }
            }
        }
    }
}
